package config

import (
	"log"

	"github.com/inntu/backend/api/models"
	"gorm.io/gorm"
)

// AutoMigrate 自动迁移数据库表结构
func AutoMigrate(db *gorm.DB) error {
	log.Println("开始数据库表结构迁移...")

	// 删除旧的中间表（如果存在）
	log.Println("清理旧的中间表...")
	db.Exec("SET FOREIGN_KEY_CHECKS = 0")
	db.Exec("DROP TABLE IF EXISTS page_categories")
	db.Exec("DROP TABLE IF EXISTS category_items")

	// 删除可能存在的外键约束
	db.Exec("ALTER TABLE items DROP FOREIGN KEY IF EXISTS items_ibfk_1")
	db.Exec("ALTER TABLE items DROP FOREIGN KEY IF EXISTS items_ibfk_2")
	db.Exec("ALTER TABLE categories DROP FOREIGN KEY IF EXISTS categories_ibfk_1")

	// 删除可能存在的约束条件
	db.Exec("ALTER TABLE items DROP CHECK chk_content_type_page")
	db.Exec("ALTER TABLE items DROP CHECK chk_content_type_markdown")
	db.Exec("ALTER TABLE items DROP CHECK chk_content_type")
	log.Println("已删除旧的约束条件")

	// 删除已移除的字段
	log.Println("删除已移除的字段...")

	// 检查并删除categories表的code字段
	var count int64
	db.Raw("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'categories' AND column_name = 'code'").Scan(&count)
	if count > 0 {
		db.Exec("ALTER TABLE categories DROP COLUMN code")
		log.Println("已删除categories表的code字段")
	}

	// 检查并删除categories表的page_id字段
	db.Raw("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'categories' AND column_name = 'page_id'").Scan(&count)
	if count > 0 {
		db.Exec("ALTER TABLE categories DROP COLUMN page_id")
		log.Println("已删除categories表的page_id字段")
	}

	// 检查并删除categories表的page_ids字段
	db.Raw("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'categories' AND column_name = 'page_ids'").Scan(&count)
	if count > 0 {
		db.Exec("ALTER TABLE categories DROP COLUMN page_ids")
		log.Println("已删除categories表的page_ids字段")
	}

	// 检查并删除items表的code字段
	db.Raw("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'items' AND column_name = 'code'").Scan(&count)
	if count > 0 {
		db.Exec("ALTER TABLE items DROP COLUMN code")
		log.Println("已删除items表的code字段")
	}

	// 检查并删除items表的category_ids字段
	db.Raw("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'items' AND column_name = 'category_ids'").Scan(&count)
	if count > 0 {
		db.Exec("ALTER TABLE items DROP COLUMN category_ids")
		log.Println("已删除items表的category_ids字段")
	}

	// 检查并删除items表的category_id字段
	db.Raw("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'items' AND column_name = 'category_id'").Scan(&count)
	if count > 0 {
		db.Exec("ALTER TABLE items DROP COLUMN category_id")
		log.Println("已删除items表的category_id字段")
	}

	db.Exec("SET FOREIGN_KEY_CHECKS = 1")

	// 自动迁移所有模型（包括中间表）
	err := db.AutoMigrate(
		&models.Page{},
		&models.Category{},
		&models.Item{},
	)

	if err != nil {
		return err
	}

	log.Println("数据库表结构迁移完成")
	return nil
}

// CreateIndexes 创建额外的索引
func CreateIndexes(db *gorm.DB) error {
	log.Println("开始创建数据库索引...")

	// 为pages表创建索引
	var count int64
	db.Raw("SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'pages' AND index_name = 'idx_pages_code_status'").Scan(&count)
	if count == 0 {
		if err := db.Exec("CREATE INDEX idx_pages_code_status ON pages(code, status)").Error; err != nil {
			log.Printf("创建pages复合索引失败: %v", err)
		}
	}

	// 为categories表创建复合索引
	db.Raw("SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'categories' AND index_name = 'idx_categories_status'").Scan(&count)
	if count == 0 {
		if err := db.Exec("CREATE INDEX idx_categories_status ON categories(status)").Error; err != nil {
			log.Printf("创建categories状态索引失败: %v", err)
		}
	}

	// 为items表创建复合索引
	db.Raw("SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'items' AND index_name = 'idx_items_parent_status'").Scan(&count)
	if count == 0 {
		if err := db.Exec("CREATE INDEX idx_items_parent_status ON items(parent_id, status)").Error; err != nil {
			log.Printf("创建items父级复合索引失败: %v", err)
		}
	}

	db.Raw("SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'items' AND index_name = 'idx_items_content_type'").Scan(&count)
	if count == 0 {
		if err := db.Exec("CREATE INDEX idx_items_content_type ON items(content_type)").Error; err != nil {
			log.Printf("创建items内容类型索引失败: %v", err)
		}
	}

	log.Println("数据库索引创建完成")
	return nil
}

// SeedData 初始化种子数据（智能检测机制）
func SeedData(db *gorm.DB) error {
	log.Println("开始检查数据库状态...")

	// 智能检测：检查数据库是否为空
	var pageCount, categoryCount, itemCount int64
	db.Model(&models.Page{}).Count(&pageCount)
	db.Model(&models.Category{}).Count(&categoryCount)
	db.Model(&models.Item{}).Count(&itemCount)

	// 如果数据库不为空，跳过种子数据初始化
	if pageCount > 0 || categoryCount > 0 || itemCount > 0 {
		log.Printf("数据库已存在数据 (Pages: %d, Categories: %d, Items: %d)，跳过种子数据初始化", pageCount, categoryCount, itemCount)
		return nil
	}

	log.Println("数据库为空，开始初始化种子数据...")

	// 重置自增ID
	db.Exec("ALTER TABLE pages AUTO_INCREMENT = 1")
	db.Exec("ALTER TABLE categories AUTO_INCREMENT = 1")
	db.Exec("ALTER TABLE items AUTO_INCREMENT = 1")

	// 调用内部种子数据创建函数
	return createSeedDataInternal(db)
}

// createSeedDataInternal 内部种子数据创建函数
func createSeedDataInternal(db *gorm.DB) error {
	// 创建页面
	pages := []models.Page{
		{Code: "home", Name: "首页", Description: stringPtr("校园小程序首页"), DisplayOrder: 1, Status: 1},
		{Code: "functions", Name: "功能大厅", Description: stringPtr("功能大厅页面"), DisplayOrder: 2, Status: 1},
		{Code: "ntu_intro", Name: "NTU介绍", Description: stringPtr("NTU介绍页面"), DisplayOrder: 3, Status: 1},
		{Code: "ntu_guide", Name: "NTU指南", Description: stringPtr("NTU指南页面"), DisplayOrder: 4, Status: 1},
		{Code: "profile", Name: "我的", Description: stringPtr("个人中心页面"), DisplayOrder: 5, Status: 1},
	}

	for i := range pages {
		if err := db.Create(&pages[i]).Error; err != nil {
			log.Printf("创建页面失败: %v", err)
			return err
		}
	}

	// 创建所有分类
	categories := []models.Category{
		// 首页和功能大厅共用的分类
		{Name: "校园服务", Description: stringPtr("校园服务分类"), DisplayOrder: 1, Status: 1},
		// 首页和NTU介绍共用的分类
		{Name: "学校介绍", Description: stringPtr("学校介绍分类"), DisplayOrder: 2, Status: 1},
		// 首页和NTU指南共用的分类
		{Name: "NTU生活", Description: stringPtr("NTU生活分类"), DisplayOrder: 3, Status: 1},
		// 功能大厅独有的分类
		{Name: "社交互动", Description: stringPtr("社交互动分类"), DisplayOrder: 4, Status: 1},
		{Name: "学习管理", Description: stringPtr("学习管理分类"), DisplayOrder: 5, Status: 1},
		// NTU介绍独有的分类
		{Name: "学院介绍", Description: stringPtr("学院介绍分类"), DisplayOrder: 6, Status: 1},
		// NTU指南独有的分类
		{Name: "你好NTU", Description: stringPtr("你好NTU分类"), DisplayOrder: 7, Status: 1},
		{Name: "学习介绍", Description: stringPtr("学习介绍分类"), DisplayOrder: 8, Status: 1},
	}

	for i := range categories {
		if err := db.Create(&categories[i]).Error; err != nil {
			log.Printf("创建分类失败: %v", err)
			return err
		}
	}

	// 创建页面与分类的关联关系
	pageCategories := []struct {
		PageID     int
		CategoryID int
	}{
		// 首页关联的分类
		{pages[0].ID, categories[0].ID}, // 首页 - 校园服务
		{pages[0].ID, categories[1].ID}, // 首页 - 学校介绍
		{pages[0].ID, categories[2].ID}, // 首页 - NTU生活

		// 功能大厅关联的分类
		{pages[1].ID, categories[0].ID}, // 功能大厅 - 校园服务
		{pages[1].ID, categories[3].ID}, // 功能大厅 - 社交互动
		{pages[1].ID, categories[4].ID}, // 功能大厅 - 学习管理

		// NTU介绍关联的分类
		{pages[2].ID, categories[1].ID}, // NTU介绍 - 学校介绍
		{pages[2].ID, categories[5].ID}, // NTU介绍 - 学院介绍

		// NTU指南关联的分类
		{pages[3].ID, categories[6].ID}, // NTU指南 - 你好NTU
		{pages[3].ID, categories[2].ID}, // NTU指南 - NTU生活
		{pages[3].ID, categories[7].ID}, // NTU指南 - 学习介绍
	}

	for _, pc := range pageCategories {
		if err := db.Exec("INSERT INTO page_categories (page_id, category_id) VALUES (?, ?)", pc.PageID, pc.CategoryID).Error; err != nil {
			log.Printf("创建页面与分类关联失败: %v", err)
			return err
		}
	}

	// 创建所有项目
	items := []models.Item{
		// 校园服务项目
		{Name: "校园论坛", Icon: "forum", ContentType: "page", DisplayOrder: 1, Status: 1},
		{Name: "公众号", Icon: "wechat", ContentType: "page", DisplayOrder: 2, Status: 1},
		{Name: "校历", Icon: "calendar", ContentType: "page", DisplayOrder: 3, Status: 1},
		{Name: "校园地图", Icon: "map", ContentType: "page", DisplayOrder: 4, Status: 1},
		{Name: "天气", Icon: "weather", ContentType: "page", DisplayOrder: 5, Status: 1},

		// 学校介绍项目
		{Name: "关于学校", Icon: "school", ContentType: "page", DisplayOrder: 1, Status: 1},
		{Name: "学校概况", Icon: "info", ContentType: "page", DisplayOrder: 2, Status: 1},
		{Name: "资助", Icon: "money", ContentType: "page", DisplayOrder: 3, Status: 1},

		// NTU生活项目
		{Name: "寝室", Icon: "home", ContentType: "page", DisplayOrder: 1, Status: 1},
		{Name: "快递", Icon: "package", ContentType: "page", DisplayOrder: 2, Status: 1},
		{Name: "洗浴", Icon: "bath", ContentType: "page", DisplayOrder: 3, Status: 1},
		{Name: "校园网", Icon: "wifi", ContentType: "page", DisplayOrder: 4, Status: 1},
		{Name: "生活", Icon: "life", ContentType: "page", DisplayOrder: 5, Status: 1},
		{Name: "交通出行", Icon: "transport", ContentType: "page", DisplayOrder: 6, Status: 1},
		{Name: "NTU食堂", Icon: "restaurant", ContentType: "page", DisplayOrder: 7, Status: 1},
		{Name: "校园超市", Icon: "shop", ContentType: "page", DisplayOrder: 8, Status: 1},

		// 社交互动项目
		{Name: "外卖", Icon: "food", ContentType: "page", DisplayOrder: 2, Status: 1},
		{Name: "互助跑腿", Icon: "help", ContentType: "page", DisplayOrder: 3, Status: 1},

		// 学习管理项目
		{Name: "课程表", Icon: "schedule", ContentType: "page", DisplayOrder: 1, Status: 1},
		{Name: "成绩查询", Icon: "grade", ContentType: "page", DisplayOrder: 2, Status: 1},

		// 学院介绍项目
		{Name: "艺术学院", Icon: "art", ContentType: "page", DisplayOrder: 1, Status: 1},
		{Name: "师范学院", Icon: "teacher", ContentType: "page", DisplayOrder: 2, Status: 1},
		{Name: "管理学院", Icon: "management", ContentType: "page", DisplayOrder: 3, Status: 1},
		{Name: "理学院", Icon: "science", ContentType: "page", DisplayOrder: 4, Status: 1},
		{Name: "文学院", Icon: "literature", ContentType: "page", DisplayOrder: 5, Status: 1},
		{Name: "工学院", Icon: "engineering", ContentType: "page", DisplayOrder: 6, Status: 1},
		{Name: "学前教育学院", Icon: "preschool", ContentType: "page", DisplayOrder: 7, Status: 1},
		{Name: "外语学院", Icon: "language", ContentType: "page", DisplayOrder: 8, Status: 1},
		{Name: "体育学院", Icon: "sports", ContentType: "page", DisplayOrder: 9, Status: 1},

		// 你好NTU项目
		{Name: "需带物品", Icon: "luggage", ContentType: "page", DisplayOrder: 1, Status: 1},
		{Name: "入学准备", Icon: "preparation", ContentType: "page", DisplayOrder: 2, Status: 1},
		{Name: "走读", Icon: "commute", ContentType: "page", DisplayOrder: 3, Status: 1},
		{Name: "学费缴纳", Icon: "payment", ContentType: "page", DisplayOrder: 4, Status: 1},
		{Name: "防盗防骗", Icon: "security", ContentType: "page", DisplayOrder: 5, Status: 1},
		{Name: "转专业", Icon: "transfer", ContentType: "page", DisplayOrder: 6, Status: 1},

		// 学习介绍项目
		{Name: "教师资格证", Icon: "certificate", ContentType: "page", DisplayOrder: 1, Status: 1},
		{Name: "图书馆", Icon: "library", ContentType: "page", DisplayOrder: 2, Status: 1},
		{Name: "英语a级考试", Icon: "english", ContentType: "page", DisplayOrder: 3, Status: 1},
	}

	for i := range items {
		if err := db.Create(&items[i]).Error; err != nil {
			log.Printf("创建项目失败: %v", err)
			return err
		}
	}

	// 创建分类与项目的关联关系
	categoryItems := []struct {
		CategoryID int
		ItemIDs    []int
	}{
		// 校园服务 - 项目关联
		{categories[0].ID, []int{1, 2, 3, 4, 5}}, // 校园论坛, 公众号, 校历, 校园地图, 天气

		// 学校介绍 - 项目关联
		{categories[1].ID, []int{6, 7, 8}}, // 关于学校, 学校概况, 资助

		// NTU生活 - 项目关联
		{categories[2].ID, []int{9, 10, 11, 12, 13, 14, 15, 16}}, // 寝室, 快递, 洗浴, 校园网, 生活, 交通出行, NTU食堂, 校园超市

		// 社交互动 - 项目关联
		{categories[3].ID, []int{1, 17, 18, 2}}, // 校园论坛, 外卖, 互助跑腿, 公众号

		// 学习管理 - 项目关联
		{categories[4].ID, []int{19, 20}}, // 课程表, 成绩查询

		// 学院介绍 - 项目关联
		{categories[5].ID, []int{21, 22, 23, 24, 25, 26, 27, 28, 29}}, // 艺术学院, 师范学院, 管理学院, 理学院, 文学院, 工学院, 学前教育学院, 外语学院, 体育学院

		// 你好NTU - 项目关联
		{categories[6].ID, []int{30, 31, 32, 33, 34, 35}}, // 需带物品, 入学准备, 走读, 学费缴纳, 防盗防骗, 转专业

		// 学习介绍 - 项目关联
		{categories[7].ID, []int{36, 37, 38}}, // 教师资格证, 图书馆, 英语a级考试
	}

	for _, ci := range categoryItems {
		for _, itemID := range ci.ItemIDs {
			if err := db.Exec("INSERT INTO category_items (category_id, item_id) VALUES (?, ?)", ci.CategoryID, itemID).Error; err != nil {
				log.Printf("创建分类与项目关联失败: %v", err)
				return err
			}
		}
	}

	log.Println("种子数据初始化完成")
	return nil
}

// ForceSeedData 强制重新初始化种子数据（清空现有数据）
func ForceSeedData(db *gorm.DB) error {
	log.Println("开始强制重新初始化种子数据...")

	// 清空现有数据并重新初始化
	log.Println("清空现有数据...")
	db.Exec("DELETE FROM category_items")
	db.Exec("DELETE FROM page_categories")
	db.Exec("DELETE FROM items")
	db.Exec("DELETE FROM categories")
	db.Exec("DELETE FROM pages")
	log.Println("现有数据已清空，开始重新初始化...")

	// 重置自增ID
	db.Exec("ALTER TABLE pages AUTO_INCREMENT = 1")
	db.Exec("ALTER TABLE categories AUTO_INCREMENT = 1")
	db.Exec("ALTER TABLE items AUTO_INCREMENT = 1")

	// 调用原有的种子数据创建逻辑
	return createSeedDataInternal(db)
}

// stringPtr 返回字符串指针
func stringPtr(s string) *string {
	return &s
}

// uintPtr 返回uint指针
func uintPtr(u uint) *uint {
	return &u
}
